package com.wyz.note.project.demo;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description: 表格工具测试类
 * @author: wei yz
 * @Date: 2024/1/28 10:27
 */
public class ExcelTest2 {

    private static List<ExcelMergedInfo> getColMergedInfo(List<Integer> mergedRowsCounts) {
        List<ExcelMergedInfo> mergedInfoList = new ArrayList<>();
        // 合并列数，合并后的行数，例如 第一列有 3 条合并行数据，有 【1】=> 2-18，【2】=> 19-34，【3】=> 35-49
        int index = 0;
        // 合并后行的数量，例如有 【1】=> 2-18，【2】=> 19-34，【3】=> 35-49，算做3
        int num = mergedRowsCounts.size();
        // 记录上一条的合并行的 合并数，合并数 例如 第2行至第18行，为 17
        int lastRow = 1;

        for (int i = 0; i < mergedRowsCounts.size(); i++) {
            ExcelMergedInfo mergedInfo = new ExcelMergedInfo();
            mergedInfo.setLevel(1);
            mergedInfo.setMergedCol(++index);
            mergedInfo.setFirstRow(i == 0 ? 2 : lastRow + 1);
            // 开始行+合并数
            mergedInfo.setLastRow(lastRow + mergedRowsCounts.get(--num));
            mergedInfo.setMergedRows(mergedRowsCounts.get(num));
            mergedInfoList.add(mergedInfo);
            lastRow = mergedInfo.getLastRow();
        }
        return mergedInfoList;
    }

    public static void main(String[] args) {
        try {
            FileInputStream fis = new FileInputStream("D:\\test\\java\\test.xlsx");
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);
            // 获取第一个工作表
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            List<Integer> mergedRowsCounts = getMergedRowsCountsFromSecondRow(xssfSheet, 0);
            // 一级
            List<ExcelMergedInfo> mergedInfoList = getColMergedInfo(mergedRowsCounts);
            for (ExcelMergedInfo mergedInfo : mergedInfoList) {
                // 获取从第2行到第18行，第二列的合并单元格信息
                // 列索引为1（第二列），行索引需要调整为从0开始
                String stringCellValue = xssfSheet.getRow(mergedInfo.getFirstRow() - 1).getCell(0).getStringCellValue();
                System.out.println(" ====> " + stringCellValue);
                List<ExcelTest2.CellInfo> cellInfos = getCellMergeInfo(xssfSheet, mergedInfo.getFirstRow() - 1, mergedInfo.getLastRow() - 1, 1);
                // 输出结果
                System.out.println("单元格信息（包括合并与非合并）:");
                for (ExcelTest2.CellInfo info : cellInfos) {
                    stringCellValue = xssfSheet.getRow(info.getRow()).getCell(2).getStringCellValue();
                    System.out.println("单元格数据：" + stringCellValue + ", 单元格行号: " + (info.getRow() + 1) + ", 合并行数: " + info.getMergedRows());

                }
            }
            System.out.println("--------------------------");
            // hhhhh
            getColMergedInfo(getMergedRowsCountsFromSecondRow(xssfSheet, 1)).forEach(item->{
                System.out.println(item);
            });

            xssfWorkbook.close();
            fis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List<Integer> getMergedRowsCountsFromSecondRow(XSSFSheet sheet, int col) {
        List<Integer> mergedRowsCounts = new ArrayList<>();
        int numMergedRegions = sheet.getNumMergedRegions();

        for (int i = 0; i < numMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstRow = mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            int firstCol = mergedRegion.getFirstColumn();

            // 检查合并区域是否在第一列，并且至少从第二行开始
            if (firstCol == col && firstRow >= 1) {
                int mergedRows = lastRow - firstRow + 1;
                mergedRowsCounts.add(mergedRows);
            }
        }

        return mergedRowsCounts;
    }


    public static List<ExcelTest2.CellInfo> getCellMergeInfo(XSSFSheet sheet, int startRow, int endRow, int colIndex) {
        List<ExcelTest2.CellInfo> cellInfos = new ArrayList<>();

        int numMergedRegions = sheet.getNumMergedRegions();
        List<Integer> mergedRows = new ArrayList<>(); // 用于标记已经合并的行

        // 先处理合并单元格
        for (int i = 0; i < numMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

            int mergedFirstRow = mergedRegion.getFirstRow();
            int mergedLastRow = mergedRegion.getLastRow();
            int mergedFirstCol = mergedRegion.getFirstColumn();
            int mergedLastCol = mergedRegion.getLastColumn();

            // 检查合并区域是否在指定的列范围内，且行范围有重叠
            if (mergedFirstCol <= colIndex && colIndex <= mergedLastCol && (mergedFirstRow <= endRow && mergedLastRow >= startRow)) {

                // 计算合并区域与指定行范围的交集
                int intersectionStartRow = Math.max(mergedFirstRow, startRow);
                int intersectionEndRow = Math.min(mergedLastRow, endRow);

                // 添加到结果列表中
                for (int j = intersectionStartRow; j <= intersectionEndRow; j++) {
                    if (!mergedRows.contains(j)) {
                        mergedRows.add(j);
                        cellInfos.add(new ExcelTest2.CellInfo(j, intersectionEndRow - intersectionStartRow + 1));
                    }
                }
            }
        }

        // 处理非合并单元格
        for (int i = startRow; i <= endRow; i++) {
            if (!mergedRows.contains(i)) {
                cellInfos.add(new ExcelTest2.CellInfo(i, 1)); // 非合并单元格合并为1行
            }
        }

        return cellInfos;
    }

    // 辅助类，用于存储单元格的行号和合并行数
    public static class CellInfo {
        private int row;
        private int mergedRows;

        public CellInfo(int row, int mergedRows) {
            this.row = row;
            this.mergedRows = mergedRows;
        }

        public int getRow() {
            return row;
        }

        public int getMergedRows() {
            return mergedRows;
        }
    }
}
